package laborum2011.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import laborum2011.excepcion.DAOExcepcion;
import laborum2011.modelo.*;
import laborum2011.util.ConexionBD;
import laborum2011.dao.BaseDAO;

public class DetEstDAO extends BaseDAO{

	public void insertar(DetalleEstudios po) throws DAOExcepcion {
		System.out.println("DetEstDAO: insertar(detalleestudios vo)");
		String query = "INSERT INTO detalleestudios(id_postulante,estudios,fecini, fecfin,tipo,estado) " +
				       "VALUES (?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, po.getId_postulante());
			stmt.setString(2,po.getEstudios());
			stmt.setTimestamp(3, po.getFecini());
			stmt.setTimestamp(4, po.getFecfin());
			stmt.setString(5, po.getTipo());
			stmt.setInt(6, po.getEstado());
			int i = stmt.executeUpdate();

			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}
	
	public Collection<DetalleEstudios> listar() throws DAOExcepcion {
		System.out.println("DetEstDAO: listar()");
		Collection<DetalleEstudios> c = new ArrayList<DetalleEstudios>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			String query = "SELECT id_postulante, estudios, fecini, fecfin, tipo, estado " +
					"FROM detalleestudios where estado=1";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				DetalleEstudios vo = new DetalleEstudios();
				vo.setId_postulante(rs.getInt("id_postulante"));
				vo.setEstudios(rs.getString("estudios"));
				vo.setFecini(rs.getTimestamp("fecini"));
				vo.setFecfin(rs.getTimestamp("fecfin"));
				vo.setTipo(rs.getString("tipo"));
				vo.setEstado(rs.getInt("estado"));
				c.add(vo);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return c;
	}	

	public List<DetalleEstudios> listarXIdpost(int id) throws DAOExcepcion {
		System.out.println("PersonaDAO: buscarPorNombre(String nombre)");
		

		List<DetalleEstudios> c = new ArrayList<DetalleEstudios>();

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "SELECT id,id_postulante, estudios, fecini, fecfin, tipo, estado " +
			"FROM detalleestudios where estado=1 and id_postulante=? ";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				DetalleEstudios vo = new DetalleEstudios();
				vo.setId(rs.getInt("id"));
				vo.setId_postulante(rs.getInt("id_postulante"));
				vo.setEstudios(rs.getString("estudios"));
				vo.setFecini(rs.getTimestamp("fecini"));
				vo.setFecfin(rs.getTimestamp("fecfin"));
				vo.setTipo(rs.getString("tipo"));
				vo.setEstado(rs.getInt("estado"));
				c.add(vo);

			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return c;
	}	

	public DetalleEstudios listarXId(int id) throws DAOExcepcion {
		System.out.println("DetEstDAO: listar()");

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		DetalleEstudios vo = new DetalleEstudios();
		try {
		
			String query = "SELECT id_postulante, estudios, fecini, fecfin, tipo, estado " +
					"FROM detalleestudios where id_postulante=? and estado=1";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				
				vo.setId_postulante(rs.getInt("id_postulante"));
				vo.setEstudios(rs.getString("estudios"));
				vo.setFecini(rs.getTimestamp("fecini"));
				vo.setFecfin(rs.getTimestamp("fecfin"));
				vo.setTipo(rs.getString("tipo"));
				vo.setEstado(rs.getInt("estado"));
				
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}	
	
	public void actualizar(DetalleEstudios po) throws DAOExcepcion {
		System.out.println("DetEstDAO: actualizar(DetEst vo)");
		String query = "update detalleestudios set " 
						+"estudios = ?," 
						+"fecini  = ?," 
						+"fecfin   = ?," 
						+"tipo   = ? " 
						+"where id =?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, po.getEstudios());
			stmt.setTimestamp(2, po.getFecini());
			stmt.setTimestamp(3, po.getFecfin());
			stmt.setString(4, po.getTipo());
			stmt.setInt(5, po.getId());
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}

	public void eliminar(int id) throws DAOExcepcion {
		System.out.println("DetEstDAO: eliminar(Int id)");
		String query = "update detalleestudios set estado = 0 where id = ?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, id);

			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo eliminar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}

	public DetalleEstudios obtenerXId1(int v) throws DAOExcepcion {
		System.out.println("DetEstDAO: listar()");

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		DetalleEstudios vo = new DetalleEstudios();
		try {
		
			String query = "SELECT id,id_postulante, estudios, fecini, fecfin, tipo, estado " +
					"FROM detalleestudios where estado=1 and id=?";
			System.out.println(v);	

			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, v);
			rs = stmt.executeQuery();
			if (rs.next()) {
				vo.setId(rs.getInt("id"));
				vo.setId_postulante(rs.getInt("id_postulante"));
				vo.setEstudios(rs.getString("estudios"));
				vo.setFecini(rs.getTimestamp("fecini"));
				vo.setFecfin(rs.getTimestamp("fecfin"));
				vo.setTipo(rs.getString("tipo"));
				vo.setEstado(rs.getInt("estado"));
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
    }

}
